﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Iit.ITBI.MDSManager.Microsoft.MasterDataServices;

namespace Iit.ITBI.MDSManager
{
    /// <summary>
    /// Acts as the Data Access Layer for the MDS Wrapper.
    /// This class will hold all the dynamic SQL commands needed to fetch data from the MDS database.
    /// The class methods will return MDSDataTables objects 
    /// </summary>
    class MDSSqlWrapper
    {
        private static MDSSqlWrapper INSTANCE = null;
        private string ConnectionString;

        public static MDSSqlWrapper GlobalInstance
        {
            get
            {
                return INSTANCE;
            }
        }

        public static void Ininitalize(string ConnectionString)
        {
            if (INSTANCE == null)
                INSTANCE = new MDSSqlWrapper(ConnectionString);
        }

        public static void Reset(string ConnectionString)
        {
            INSTANCE = new MDSSqlWrapper(ConnectionString);
        }

        private SqlConnection sqlcn;
        private SqlCommand sqlcmd;

        private StringBuilder BasicQuery;
        private StringBuilder AttributeListQuery;

        private MDSSqlWrapper(string ConnectionString)
        {
            try
            {
                this.ConnectionString = ConnectionString;

                SqlConnectionReset();

                SetBasicQuery();

                SetAttributeListQuery();

            }
            catch (Exception e)
            {
                ////Do Something
                throw;
            }
        }

        private void SetBasicQuery()
        {
            this.BasicQuery = new StringBuilder();

            BasicQuery.AppendLine("declare @SelectClause as nvarchar(max);");
            BasicQuery.AppendLine("declare @FromClause as nvarchar(max);");
            BasicQuery.AppendLine("declare @SelectClauseSufix as nvarchar(max);");
            BasicQuery.AppendLine("declare @JoinClause as nvarchar(max);");
            BasicQuery.AppendLine("declare @CrossApplyClause as nvarchar(max);");
            BasicQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t");
            BasicQuery.AppendLine("declare @SQL as nvarchar(max);");
            BasicQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\t");
            BasicQuery.AppendLine("select ");
            BasicQuery.AppendLine("\t@SelectClause = case");
            BasicQuery.AppendLine("\t\t\t\t\t\twhen @SelectClause is null then '[' + [ta].[TableColumn] + '] as [' + [ta].[Name] + '_ID],' + char(13) + ' [' + [te_ref].[EntityTable] + '].[Name] as [' + [ta].[Name] + '],' + char(13) + ' [' + [te_ref].[EntityTable] + '].[Code] as [' + [ta].[Name] + '_Code]'");
            BasicQuery.AppendLine("\t\t\t\t\t\twhen @SelectClause is not null then @SelectClause + ',' + char(13) + '[' + [ta].[TableColumn] + '] as [' + [ta].[Name] + '_ID],' + char(13) + ' [' + [te_ref].[EntityTable] + '].[Name] as [' + [ta].[Name] + '],' + char(13) + ' [' + [te_ref].[EntityTable] + '].[Code] as [' + [ta].[Name] + '_Code]'");
            BasicQuery.AppendLine("\t\t\t\t\tend,");
            BasicQuery.AppendLine("\t@FromClause = 'from mdm.' + [te].[EntityTable],");
            BasicQuery.AppendLine("\t@SelectClauseSufix = ',MappingState = case [' + [te].[EntityTable] + '].Status_ID when 1 then ''Valid'' when 2 then ''Deleted'' else ''Not Supported'' end, [' + [te].[EntityTable] + '].Code as Mapping_Code',");
            BasicQuery.AppendLine("\t@JoinClause = case");
            BasicQuery.AppendLine("\t\t\t\t\t\twhen @JoinClause is null then 'inner join mdm.[' + [te_ref].[EntityTable] + ']' + char(13) + '\ton [' + [te].[EntityTable] + '].[' + [ta].[TableColumn] + '] = [' + [te_ref].[EntityTable] + '].[ID]'");
            BasicQuery.AppendLine("\t\t\t\t\t\twhen @JoinClause is not null then @JoinClause + char(13) + 'inner join mdm.[' + [te_ref].[EntityTable] + ']' + char(13) + '\ton [' + [te].[EntityTable] + '].[' + [ta].[TableColumn] + '] = [' + [te_ref].[EntityTable] + '].[ID]'");
            BasicQuery.AppendLine("\t\t\t\t\tend,");
            BasicQuery.AppendLine("\t@CrossApplyClause = case");
            BasicQuery.AppendLine("\t\t\t\t\t\twhen @CrossApplyClause is null then 'inner join mdm.[' + [te_ref].[EntityTable] + ']' + char(13) + '\ton [' + [te].[EntityTable] + '].[' + [ta].[TableColumn] + '] = [' + [te_ref].[EntityTable] + '].[ID]'");
            BasicQuery.AppendLine("\t\t\t\t\t\twhen @CrossApplyClause is not null then @CrossApplyClause + char(13) + 'inner join mdm.[' + [te_ref].[EntityTable] + ']' + char(13) + '\ton [' + [te].[EntityTable] + '].[' + [ta].[TableColumn] + '] = [' + [te_ref].[EntityTable] + '].[ID]'");
            BasicQuery.AppendLine("\t\t\t\t\tend");
            BasicQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\t");
            BasicQuery.AppendLine("from [mdm].[tblAttribute] as [ta]");
            BasicQuery.AppendLine("inner join [mdm].[tblEntity] as [te]");
            BasicQuery.AppendLine("\ton [ta].[Entity_ID] = [te].[ID]");
            BasicQuery.AppendLine("inner join [mdm].[tblEntity] as [te_ref]");
            BasicQuery.AppendLine("\ton [ta].[DomainEntity_ID] = [te_ref].[ID]");
            BasicQuery.AppendLine("inner join [mdm].[tblModel] as [tm]");
            BasicQuery.AppendLine("\ton [te].[Model_ID] = [tm].[ID]\t");
            BasicQuery.AppendLine("where [te].[Name] = 'MasterToSlaveMapping'");
            BasicQuery.AppendLine("and [ta].[IsSystem] = 0");
            BasicQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t");
            BasicQuery.AppendLine("set @SQL = '");
            BasicQuery.AppendLine("with");
            BasicQuery.AppendLine("\tEntities as (select [te].[Name] as [Entity_Name]");
            BasicQuery.AppendLine("\t\t\t\t\t\t,[te].[ID] as [Entity_ID]");
            BasicQuery.AppendLine("\t\t\t\t\t\t,[te].[MUID] as Entity_MUID");
            BasicQuery.AppendLine("\t\t\t\t\t\t,[tm].[Name] as [Model_Name]");
            BasicQuery.AppendLine("\t\t\t\t\t\t,[tm].[ID] as [Model_ID]");
            BasicQuery.AppendLine("\t\t\t\t\t\t,[tm].[MUID] as Model_MUID");
            BasicQuery.AppendLine("\t\t\t\t\t\t,[te].[EntityTable]");
            BasicQuery.AppendLine("\t\t\t\t\t\t,[te].[StagingBase]");
            BasicQuery.AppendLine("\t\t\tfrom [mdm].[tblEntity] as [te]");
            BasicQuery.AppendLine("\t\t\tinner join [mdm].[tblModel] as [tm]");
            BasicQuery.AppendLine("\t\t\t\ton [te].[Model_ID] = [tm].[ID]),");
            BasicQuery.AppendLine("\tMasterToSlaveMappings as (select ' + @SelectClause + '");
            BasicQuery.AppendLine("\t\t\t\t\t\t\t\t' + @SelectClauseSufix + '");
            BasicQuery.AppendLine("\t\t\t\t\t\t\t\t' + @FromClause + '");
            BasicQuery.AppendLine("\t\t\t\t\t\t\t\t' + @JoinClause + '),");
            BasicQuery.AppendLine("\t\tMasterToSlaveMappingsEnriched as (select [entity].[Model_Name] as MasterModel_Name");
            BasicQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\t,[entity].[Model_MUID] as MasterModel_MUID");
            BasicQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\t,[entity].[Entity_ID] as [MasterEntity_ID]");
            BasicQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\t,[map].[Master Entity_Code] as [MasterEntity_Code]");
            BasicQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\t,[entity].[Entity_Name] as MasterEntity_Name");
            BasicQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\t,[entity].[Entity_MUID] as MasterEntity_MUID");
            BasicQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\t,coalesce([slave].[Model_Name],[map].[Slave Model]) as SlaveModel_Name");
            BasicQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\t,coalesce([slave].[Model_ID], [map].[Slave Model_ID]) as SlaveModel_ID");
            BasicQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\t,coalesce([slave].[Model_MUID],[tm].[MUID]) as SlaveModel_MUID");
            BasicQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\t,coalesce([slave].[Entity_Name], replace([Master Entity] + ''_'' +  [entity].[Model_Name] + ''_Slave'','' '','''')) as SlaveEntity_Name");
            BasicQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\t,[slave].[Entity_ID] as SlaveEntity_ID");
            BasicQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\t,[slave].[Entity_MUID] as SlaveEntity_MUID");
            BasicQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\t,MappingState");
            BasicQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\t,Mapping_Code");
            BasicQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\t,[entity].[EntityTable] as [MasterEntityTable]");
            BasicQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\t,[slave].[EntityTable] as [SlaveEntityTable]");
            BasicQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\t,[entity].[StagingBase] as [MasterEntityStagingBase]");
            BasicQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\t,[slave].[StagingBase] as [SlaveEntityStagingBase]");
            BasicQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\tfrom [MasterToSlaveMappings] as map");
            BasicQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\tcross apply [mdq].[Split]([Master Entity_Code],''_'',0,0,0) as [s]");
            BasicQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\tinner join [Entities] as entity");
            BasicQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\t\ton [entity].[Entity_ID] = [s].[Token]");
            BasicQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\t\tand s.[Sequence] = 2");
            BasicQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\tinner join [mdm].[tblModel] as [tm]");
            BasicQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\t\ton [map].[Slave Model] = [tm].[Name]");
            BasicQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\tleft outer join Entities as slave");
            BasicQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\t\ton [slave].[Entity_Name] = replace([Master Entity] + ''_'' +  [entity].[Model_Name] + ''_Slave'','' '','''')");
            BasicQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\t\tand [slave].[Model_Name] = [Slave Model])'");


        }

        private void SetAttributeListQuery()
        {
            this.AttributeListQuery = new StringBuilder();

            AttributeListQuery.AppendLine("set @SQL = @SQL + ',");
            AttributeListQuery.AppendLine("\tAttributes as (select [ta].[ID] as Attribue_ID");
            AttributeListQuery.AppendLine("\t\t\t\t\t\t,[ta].[MUID] as Attribue_MUID");
            AttributeListQuery.AppendLine("\t\t\t\t\t\t,[ta].[Entity_ID] as [Entity_ID]");
            AttributeListQuery.AppendLine("\t\t\t\t\t\t,[ta].[Name] as Attribue_Name");
            AttributeListQuery.AppendLine("\t\t\t\t\t\t,hashbytes(''SHA2_256'',");
            AttributeListQuery.AppendLine("\t\t\t\t\t\t\t\t\tconcat( isnull(cast([ta].[MemberType_ID]as varchar(100)) ,'''')+''|''");
            AttributeListQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\t,isnull(cast([ta].[DisplayName]as varchar(100)) ,'''')+''|''");
            AttributeListQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\t,isnull(cast([ta].[Name]as varchar(100)) ,'''')+''|''");
            AttributeListQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\t,isnull(cast([ta].[AttributeType_ID]as varchar(100)) ,'''')+''|''");
            AttributeListQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\t,isnull(cast([ta].[DataType_ID]as varchar(100)) ,'''')+''|''");
            AttributeListQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\t,isnull(cast([ta].[DataTypeInformation]as varchar(100)) ,'''')+''|''");
            AttributeListQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\t,isnull(cast([ta].[InputMask_ID]as varchar(100)) ,'''')+''|''");
            AttributeListQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\t,isnull(cast([ta].[DisplayWidth]as varchar(100)) ,'''')+''|''");
            AttributeListQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\t,isnull(cast([ta].[SortOrder]as varchar(100)) ,'''')+''|''");
            AttributeListQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\t,isnull(cast([ta].[IsReadOnly]as varchar(100)) ,'''')+''|''");
            AttributeListQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\t,isnull(cast([ta].[IsRequired]as varchar(100)) ,'''')+''|'')) as Attribue_HashByteValue\t");
            AttributeListQuery.AppendLine("\t\t\t\t\tfrom [mdm].[tblAttribute] as [ta]");
            AttributeListQuery.AppendLine("\t\t\t\t\tinner join MasterToSlaveMappingsEnriched as m2se");
            AttributeListQuery.AppendLine("\t\t\t\t\t\ton [ta].[Entity_ID] = [m2se].[MasterEntity_ID]");
            AttributeListQuery.AppendLine("\t\t\t\t\t\tor [ta].[Entity_ID] = [m2se].[SlaveEntity_ID]");
            AttributeListQuery.AppendLine("\t\t\t\t\twhere [ta].[IsSystem] = 0),");
            AttributeListQuery.AppendLine("\tMasterAttribues as (select [ta].[Attribue_ID] as MasterAttribute_ID");
            AttributeListQuery.AppendLine("\t\t\t\t\t\t\t,[ta].[Attribue_MUID] as MasterAttribute_MUID");
            AttributeListQuery.AppendLine("\t\t\t\t\t\t\t,[ta].[Entity_ID] as MasterEntity_ID");
            AttributeListQuery.AppendLine("\t\t\t\t\t\t\t,[m2se].[MasterEntity_Name]");
            AttributeListQuery.AppendLine("\t\t\t\t\t\t\t,[ta].[Attribue_Name] as MasterAttribute_Name");
            AttributeListQuery.AppendLine("\t\t\t\t\t\t\t,[ta].[Attribue_HashByteValue] as MasterAttribute_HashByteValue");
            AttributeListQuery.AppendLine("\t\t\t\t\t\t\t,m2se.Mapping_Code");
            AttributeListQuery.AppendLine("\t\t\t\t\t\tfrom [Attributes] as [ta]");
            AttributeListQuery.AppendLine("\t\t\t\t\t\tinner join MasterToSlaveMappingsEnriched as m2se");
            AttributeListQuery.AppendLine("\t\t\t\t\t\t\ton [ta].[Entity_ID] = [m2se].[MasterEntity_ID]),");
            AttributeListQuery.AppendLine("\tSlaveAttribues as (select [ta].[Attribue_ID] as SlaveAttribute_ID");
            AttributeListQuery.AppendLine("\t\t\t\t\t\t\t,[ta].[Attribue_MUID] as SlaveAttribute_MUID");
            AttributeListQuery.AppendLine("\t\t\t\t\t\t\t,[ta].[Entity_ID] as SlaveEntity_ID");
            AttributeListQuery.AppendLine("\t\t\t\t\t\t\t,[m2se].[SlaveEntity_Name]");
            AttributeListQuery.AppendLine("\t\t\t\t\t\t\t,[ta].[Attribue_Name] as SlaveAttribute_Name");
            AttributeListQuery.AppendLine("\t\t\t\t\t\t\t,[ta].[Attribue_HashByteValue] as SlaveAttribute_HashByteValue");
            AttributeListQuery.AppendLine("\t\t\t\t\t\t\t,m2se.Mapping_Code");
            AttributeListQuery.AppendLine("\t\t\t\t\t\tfrom [Attributes] as [ta]");
            AttributeListQuery.AppendLine("\t\t\t\t\t\tinner join MasterToSlaveMappingsEnriched as m2se");
            AttributeListQuery.AppendLine("\t\t\t\t\t\t\ton [ta].[Entity_ID] = [m2se].[SlaveEntity_ID]),");
            AttributeListQuery.AppendLine("\tmerged as (select distinct [ma].[MasterAttribute_ID]");
            AttributeListQuery.AppendLine("\t\t\t\t\t,[ma].[MasterAttribute_MUID]");
            AttributeListQuery.AppendLine("\t\t\t\t\t,[ma].[MasterEntity_ID]");
            AttributeListQuery.AppendLine("\t\t\t\t\t,[ma].[MasterAttribute_Name]");
            AttributeListQuery.AppendLine("\t\t\t\t\t,[ma].[MasterAttribute_HashByteValue]");
            AttributeListQuery.AppendLine("\t\t\t\t\t,[ma].[MasterEntity_Name]");
            AttributeListQuery.AppendLine("\t\t\t\t\t,[sa].[SlaveAttribute_ID]");
            AttributeListQuery.AppendLine("\t\t\t\t\t,[sa].[SlaveAttribute_MUID]");
            AttributeListQuery.AppendLine("\t\t\t\t\t,[sa].[SlaveEntity_ID]");
            AttributeListQuery.AppendLine("\t\t\t\t\t,[sa].[SlaveEntity_Name]");
            AttributeListQuery.AppendLine("\t\t\t\t\t,[sa].[SlaveAttribute_Name]");
            AttributeListQuery.AppendLine("\t\t\t\t\t,[sa].[SlaveAttribute_HashByteValue]");
            AttributeListQuery.AppendLine("\t\t\t\t\t,AttributeSyncState = case");
            AttributeListQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\twhen [ma].[MasterAttribute_HashByteValue] = [sa].[SlaveAttribute_HashByteValue] then 1 --Identical");
            AttributeListQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\twhen [ma].[MasterAttribute_MUID] is null and [sa].[SlaveAttribute_MUID] is not null then 2 -- OnlyInSlave");
            AttributeListQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\twhen [sa].[SlaveAttribute_MUID] is null and [ma].[MasterAttribute_MUID] is not null then 3 -- OnlyInMaster");
            AttributeListQuery.AppendLine("\t\t\t\t\t\t\t\t\t\t\twhen [sa].[SlaveAttribute_MUID] is null and [ma].[MasterAttribute_MUID] is null then 4 --Invalid");
            AttributeListQuery.AppendLine("\t\t\t\t\t\t\t\t\t\telse 5 --Different");
            AttributeListQuery.AppendLine("\t\t\t\t\t\t\t\t\tend");
            AttributeListQuery.AppendLine("\t\t\t\t\t\t\t\t\t,coalesce(ma.Mapping_Code,sa.Mapping_Code) as Mapping_Code");            
            AttributeListQuery.AppendLine("\t\t\t\tfrom [MasterAttribues] as ma");
            AttributeListQuery.AppendLine("\t\t\t\tfull outer join [SlaveAttribues] as sa\t");
            AttributeListQuery.AppendLine("\t\t\t\t\ton [ma].[MasterAttribute_Name] = [sa].[SlaveAttribute_Name]");
            AttributeListQuery.AppendLine("\t\t\t\t\tand [ma].[Mapping_Code] = [sa].[Mapping_Code])'");

        }

        /// <summary>
        /// Fetch the master to slave list from the MDS database. 
        /// Encapsulates the logic needed to allocate the correct metadata tables in the MDS database, and check
        /// to see if the slave Entity exists.
        /// </summary>
        /// <returns><see cref="CustomDataTable">MDSDataTable</see></returns>
        public CustomDataTable GetMasterToSlaveList()
        {
            StringBuilder Command = new StringBuilder();

            Command.Append(BasicQuery.ToString());

            Command.AppendLine("set @SQL = @SQL + '");
            Command.AppendLine("select [map].[MasterModel_Name]");
            Command.AppendLine("      ,[map].[MasterModel_MUID]");
            Command.AppendLine("      ,[map].[MasterEntity_ID]");
            Command.AppendLine("      ,[map].[MasterEntity_Code]");
            Command.AppendLine("      ,[map].[MasterEntity_Name]");
            Command.AppendLine("      ,[map].[MasterEntity_MUID]");
            Command.AppendLine("      ,[map].[SlaveModel_Name]");
            Command.AppendLine("      ,[map].[SlaveModel_ID]");
            Command.AppendLine("      ,[map].[SlaveModel_MUID]");
            Command.AppendLine("      ,[map].[SlaveEntity_Name]");
            Command.AppendLine("      ,[map].[SlaveEntity_ID]");
            Command.AppendLine("      ,[map].[SlaveEntity_MUID]");
            Command.AppendLine("      ,[map].[MappingState]");
            Command.AppendLine("      ,[map].[Mapping_Code]");
            Command.AppendLine("      ,[map].[MasterEntityTable]");
            Command.AppendLine("      ,[map].[SlaveEntityTable]");
            Command.AppendLine("      ,[map].[MasterEntityStagingBase]");
            Command.AppendLine("      ,[map].[SlaveEntityStagingBase]");

            Command.AppendLine("from [MasterToSlaveMappingsEnriched] as map';");
            Command.AppendLine("exec sp_executesql @SQL");
            
            return GetDataTable(ExecuteReader(Command.ToString()), MDSQueryVersion.GetMasterToSlaveMappingV1);                    
        }

        /// <summary>
        /// Fetch the aggreagated sync state for each Entity in the master to slave mapping. 
        /// Encapsulates the logic needed to allocate differences in any of the Entity attributes
        /// </summary>
        /// <returns><see cref="CustomDataTable">MDSDataTable</see></returns>
        public CustomDataTable EntitiesSyncState()
        {
            StringBuilder Command = new StringBuilder();

            Command.Append(BasicQuery.ToString());
            Command.Append(AttributeListQuery.ToString());

            Command.AppendLine("set @SQL = @SQL + '");
            Command.AppendLine("select [merged].[Mapping_Code]");
            Command.AppendLine("\t,EntitySyncState = case ");
            Command.AppendLine("\t\t\t\t\t\t\t\twhen max([merged].[AttributeSyncState]) = 1 then 1");
            Command.AppendLine("\t\t\t\t\t\t\t\twhen max([merged].[AttributeSyncState]) > 1 and max([merged].[SlaveEntity_ID]) is null then 2");
            Command.AppendLine("\t\t\t\t\t\t\t\telse 3");
            Command.AppendLine("\t\t\t\t\t\t\tend");
            Command.AppendLine("from [merged]");
            Command.AppendLine("group by [merged].[Mapping_Code]'");

            Command.AppendLine("exec sp_executesql @SQL");

            return GetDataTable(ExecuteReader(Command.ToString()), MDSQueryVersion.GetEntitySyncStateV1);
        }

        /// <summary>
        /// Fetch the detailed list of the attributes, for each Entity in the master to slave mapping.
        /// Encapsulates the logic needed to allocate differences in any of the Entity attributes.
        /// </summary>
        /// <returns><see cref="CustomDataTable">MDSDataTable</see></returns>
        public CustomDataTable GetAttributeDetails()
        {
            StringBuilder Command = new StringBuilder();

            Command.Append(BasicQuery.ToString());
            Command.Append(AttributeListQuery.ToString());

            Command.AppendLine("set @SQL = @SQL + '");
            Command.AppendLine("select [merged].[MasterAttribute_ID]");
            Command.AppendLine("\t\t,[merged].[MasterAttribute_MUID]");
            Command.AppendLine("\t\t,[merged].[MasterEntity_ID]");
            Command.AppendLine("\t\t,[merged].[MasterEntity_Name]");
            Command.AppendLine("\t\t,[merged].[MasterAttribute_Name]");
            Command.AppendLine("\t\t,[merged].[MasterAttribute_HashByteValue]");
            Command.AppendLine("\t\t,[merged].[SlaveAttribute_ID]");
            Command.AppendLine("\t\t,[merged].[SlaveAttribute_MUID]");
            Command.AppendLine("\t\t,[merged].[SlaveEntity_ID]");
            Command.AppendLine("\t\t,[merged].[SlaveEntity_Name]");
            Command.AppendLine("\t\t,[merged].[SlaveAttribute_Name]");
            Command.AppendLine("\t\t,[merged].[SlaveAttribute_HashByteValue]");
            Command.AppendLine("\t\t,[merged].[AttributeSyncState]");
            Command.AppendLine("\t\t,[merged].[Mapping_Code]");
            Command.AppendLine("from [merged]'");
            
            Command.AppendLine("exec sp_executesql @SQL");


            try
            {
                return GetDataTable(ExecuteReader(Command.ToString()), MDSQueryVersion.GetAttributeDetailesV1);
            }
            catch (Exception e)
            {
                throw;
            }
            finally
            {
                 
            }
        }

        /// <summary>
        /// Deletes the members that exists in the slave entity but not in the master entity.
        /// </summary>
        /// <param name="MasterEntityTable">The master entity table name.</param>
        /// <param name="SlaveEntityTable">The slave entity table name.</param>
        /// <returns><see cref="OperationResult">OperationResult</see></returns>
        public OperationResult DeleteMembersFromSlaveEntity(string MasterEntityTable, string SlaveEntityTable, string SlaveVersionName, string SlaveStagingBase)
        {
            
            StringBuilder Command = new StringBuilder();
            
            Command.AppendLine("\tdeclare @BtachTag varchar(50);");
            Command.AppendLine("\tset @BtachTag = N'DEL Slave' + cast(newid() as varchar(50))");
            Command.AppendLine("");
            Command.AppendLine("\t/*");
            Command.AppendLine("\tloading the Codes that exists in the slave but not the master entity");
            Command.AppendLine("\tto the slave entity staging table with the ImportType 6");
            Command.AppendLine("");
            Command.AppendLine("\tThe ImportType 5 does the following:");
            Command.AppendLine("\t\"Permanently delete the member, based on the Code value. All attributes, hierarchy and ");
            Command.AppendLine("\tcollection memberships, and transactions are permanently deleted. ");
            Command.AppendLine("\tIf the member is used as a domain-based attribute value of other members, ");
            Command.AppendLine("\tthe related values will be set to NULL. ImportType 6 is for leaf members only.\"");
            Command.AppendLine("\t*/");
            Command.AppendLine("\tinsert into stg.[" + SlaveStagingBase + "_Leaf]");
            Command.AppendLine("\t\t\t([ImportType]");
            Command.AppendLine("\t\t\t,[BatchTag]");
            Command.AppendLine("\t\t\t,[Code])");
            Command.AppendLine("\tselect distinct 6 -- Permanently delete ");
            Command.AppendLine("\t\t\t\t\t,@BtachTag");
            Command.AppendLine("\t\t\t\t\t,[SlaveEntityTable].[Code]");
            Command.AppendLine("from [mdm].[" + MasterEntityTable + "] as MasterEntityTable");
            Command.AppendLine("right outer join [mdm].[" + SlaveEntityTable + "] as SlaveEntityTable");
            Command.AppendLine("\t\ton [MasterEntityTable].[Code] = [SlaveEntityTable].[Code]");
            Command.AppendLine("\twhere [MasterEntityTable].[Code] is null;");
            Command.AppendLine("");
            Command.AppendLine("\tEXEC[stg].[udp_" + SlaveStagingBase + "_Leaf]");
            Command.AppendLine("\t\t@VersionName = N'" + SlaveVersionName + "',");
            Command.AppendLine("\t\t@LogFlag = 1, --Determines whether transactions are logged during the staging process. 1: Log transactions");
            Command.AppendLine("\t\t@BatchTag = @BtachTag");
            Command.AppendLine("");
            Command.AppendLine("\t--output any errors that may occure");
            Command.AppendLine("\tselect stg.[ID]");
            Command.AppendLine("\t\t,stg.[ImportType]");
            Command.AppendLine("\t\t,stg.[ImportStatus_ID]");
            Command.AppendLine("\t\t,stg.[Batch_ID]");
            Command.AppendLine("\t\t,stg.[BatchTag]");
            Command.AppendLine("\t\t,stg.[ErrorCode]");
            Command.AppendLine("\t\t,stg.[Code] as MemberCode");
            Command.AppendLine("\t\t,errinf.[Text] as ErrorText");
            Command.AppendLine("\t\t,errinf.[Category] as ErrorCategory");
            Command.AppendLine("\tfrom stg.[" + SlaveStagingBase + "_Leaf] as stg");
            Command.AppendLine("\tleft join [mdm].[tblDBErrors] as errinf");
            Command.AppendLine("\t\ton stg.[ErrorCode] = errinf.[ID]");
            Command.AppendLine("\twhere stg.[BatchTag] = @BtachTag");
            Command.AppendLine("\tand stg.[ImportStatus_ID] = 2 -- import status ID 2 = a failure");
            Command.AppendLine("\tand errinf.[Language_ID] = 1033; --only english errors are taken to prevent duplicates of the same error code");

            try
            {
                return GetDataTable(ExecuteReader(Command.ToString()), MDSQueryVersion.DeletedMembersV1).ToOperationResult();
            }
            catch (Exception e)
            {
                throw;
            }
            finally
            {

            }
        
        }


        /// <summary>
        /// Delete a soft deleted mapping permenantly.
        /// When one deletes a row of an entity in MDS, it is not really deleted. This intefere with the process of this MDS Manager application
        /// To mitigate this, we add this code to force delete the mapping after the slave entity is removed.
        /// </summary>
        /// <param name="MappingCode">The mapping code which is the code of the row in the mapping entity.</param>
        /// <returns></returns>
        public OperationResult PermenantDeleteMapping(string MappingCode)
        {

            StringBuilder Command = new StringBuilder();

            Command.AppendLine("declare @BtachTag varchar(50);");
            Command.AppendLine("set @BtachTag = N'DEL Mapping' + cast(newid() as varchar(50))");
            Command.AppendLine("            ");
            Command.AppendLine("/*");
            Command.AppendLine("            ");
            Command.AppendLine("The ImportType 5 does the following:");
            Command.AppendLine("\"Permanently delete the member, based on the Code value. All attributes, hierarchy and ");
            Command.AppendLine("collection memberships, and transactions are permanently deleted. ");
            Command.AppendLine("If the member is used as a domain-based attribute value of other members, ");
            Command.AppendLine("the related values will be set to NULL. ImportType 6 is for leaf members only.\"");
            Command.AppendLine("*/");
            Command.AppendLine("insert into stg.[MasterToSlaveMapping_Leaf]");
            Command.AppendLine("        ([ImportType]");
            Command.AppendLine("        ,[BatchTag]");
            Command.AppendLine("        ,[Code])");
            Command.AppendLine("select distinct 6 -- Permanently delete ");
            Command.AppendLine("            \t,@BtachTag");
            Command.AppendLine("            \t," + MappingCode);
            Command.AppendLine("            ");
            Command.AppendLine("EXEC[stg].[udp_MasterToSlaveMapping_Leaf]");
            Command.AppendLine("    @VersionName = N'VERSION 1',");
            Command.AppendLine("    @LogFlag = 1, --Determines whether transactions are logged during the staging process. 1: Log transactions");
            Command.AppendLine("    @BatchTag = @BtachTag");
            Command.AppendLine("            ");
            Command.AppendLine("--output any errors that may occure");
            Command.AppendLine("select stg.[ID]");
            Command.AppendLine("    ,stg.[ImportType]");
            Command.AppendLine("    ,stg.[ImportStatus_ID]");
            Command.AppendLine("    ,stg.[Batch_ID]");
            Command.AppendLine("    ,stg.[BatchTag]");
            Command.AppendLine("    ,stg.[ErrorCode]");
            Command.AppendLine("    ,stg.[Code] as MemberCode");
            Command.AppendLine("    ,errinf.[Text] as ErrorText");
            Command.AppendLine("    ,errinf.[Category] as ErrorCategory");
            Command.AppendLine("from stg.[MasterToSlaveMapping_Leaf] as stg");
            Command.AppendLine("left join [mdm].[tblDBErrors] as errinf");
            Command.AppendLine("    on stg.[ErrorCode] = errinf.[ID]");
            Command.AppendLine("where stg.[BatchTag] = @BtachTag");
            Command.AppendLine("and stg.[ImportStatus_ID] = 2 -- import status ID 2 = a failure");
            Command.AppendLine("and errinf.[Language_ID] = 1033; --only english errors are taken to prevent duplicates of the same error code");
            Command.AppendLine("");


            try
            {
                return GetDataTable(ExecuteReader(Command.ToString()), MDSQueryVersion.DeletedMembersV1).ToOperationResult();
            }
            catch (Exception e)
            {
                throw;
            }
            finally
            {

            }

        }

        /// <summary>
        /// Gets the identifiers of any modified attributes between the slave and master. The identifiers are of the master entity.
        /// </summary>
        /// <param name="MasterEntityTable">The master entity table.</param>
        /// <param name="SlaveEntityTable">The slave entity table.</param>
        /// <returns>
        /// Array of identifiers.
        /// </returns>
        public string GetModifiedAttributesCodes(string MasterEntityTable, string SlaveEntityTable)
        {

            StringBuilder Command = new StringBuilder();
            
            ///this value specifiy the lag back that will be taken between the two entities. 
            ///We need the lag as the sync of the two entities is async, that is not in one atomic transaction, 
            ///and it might be the case that between the retrival of the list and the actual sync, 
            ///there is another change made to the same row. the lag allows us to cover that, yet it also means that we 
            ///may be "replicating" the same row more then once.
            String TimeLag = Properties.Settings.Default.ModifiedAttributesTimeLagInMinutes.ToString();
            
            ///this query will list new rows in the master, or existing matching rows, 
            ///that have a last change datetime in the master entity bigger than the 
            ///slave entity last chnaged datetime.
            ///Note that we change the slave time with the lag value, to mitigate for async issues.
            Command.AppendLine("SELECT MasterEntityTable.Code");
            Command.AppendLine("FROM [mdm].[" + MasterEntityTable + "] AS MasterEntityTable");
            Command.AppendLine("LEFT OUTER JOIN [mdm].[" + SlaveEntityTable + "] AS SlaveEntityTable");
            Command.AppendLine("\tON [MasterEntityTable].[Code] = [SlaveEntityTable].[Code]");
            Command.AppendLine("WHERE MasterEntityTable.LastChgDTM > DATEADD(MINUTE,-" + TimeLag + ",SlaveEntityTable.LastChgDTM)");
            Command.AppendLine("OR SlaveEntityTable.MUID IS NULL");

            
            try
            {

                //get the list of GUIDS
                SqlDataReader dr = ExecuteReader(Command.ToString());

                if (dr.HasRows == false)
                {
                    return null;
                }
                else
                {

                    List<string> ModifiedAttributesCodesList = new List<string>();
                    
                    //iterate over the return rows and add each row to the list of codes
                    while (dr.Read())
                    {
                        ModifiedAttributesCodesList.Add("'" + dr[0].ToString() + "'");
                    }
                    
                    //Return the codes as a comma seperated list
                    return string.Join(",",ModifiedAttributesCodesList);
                }
            }
            catch
            {
                throw;
            }
            finally
            {
            }

        }

        /// <summary>
        /// Executes masterAttributeMetadata SqlCommand of type Text, using the supplied command string and returns masterAttributeMetadata SqlDataReader.
        /// </summary>
        /// <param name="Command">A string holding the SQL code to execute.</param>
        /// <returns>A SqlDataReader as the result of the SqlCommand ExecuteReader method.</returns>
        private SqlDataReader ExecuteReader(string Command)
        {
         
            try
            {

                SqlConnectionReset();

                sqlcmd = new SqlCommand();

                this.sqlcmd.CommandText = Command;
                this.sqlcmd.CommandType = CommandType.Text;
                this.sqlcmd.Connection = sqlcn;

                return this.sqlcmd.ExecuteReader();
            }
            catch (Exception e)
            {
                throw;
            
            }
            finally
            {
            
            }

        }
        
        /// <summary>
        /// A generic method that convert the result of any SqlDataReader to masterAttributeMetadata MDSDataTable auto generating the data table schema on the fly.
        /// </summary>
        /// <param name="dr">The SqlDataReader that holds the data.</param>
        /// <param name="QueryVersion">The query version. A query version acts as masterAttributeMetadata contract between the creator of the MDSDataTable and the users of the MDSDataTable.
        /// Each query version indicate an agreement on the actual data table schema.</param>
        /// <returns><see cref="CustomDataTable">MDSDataTable</see></returns>
        private CustomDataTable GetDataTable(SqlDataReader dr, MDSQueryVersion QueryVersion)
        {
            try
            {
                DataTable dtSchema = dr.GetSchemaTable();
                CustomDataTable dt = new CustomDataTable(QueryVersion);
                
                List<DataColumn> listCols = new List<DataColumn>();

                if (dtSchema != null)
                {
                    foreach (DataRow drow in dtSchema.Rows)
                    {
                        
                        string columnName = System.Convert.ToString(drow["ColumnName"]);

                        DataColumn column = new DataColumn(columnName, (Type)(drow["DataType"]));
                        column.Unique = (bool)drow["IsUnique"];
                        column.AllowDBNull = (bool)drow["AllowDBNull"];
                        column.AutoIncrement = (bool)drow["IsAutoIncrement"];
                        listCols.Add(column);
                        dt.Columns.Add(column);
                    }
                }

                // Read rows from DataReader and populate the DataTable
                while (dr.Read())
                {
                    DataRow dataRow = dt.NewRow();
                    for (int i = 0; i < listCols.Count; i++)
                    {
                        dataRow[((DataColumn)listCols[i])] = dr[i];
                    }
                    dt.Rows.Add(dataRow);
                }

                return dt;
            }
            catch (SqlException e)
            {
                throw;
            }
            catch (Exception ex)
            {
                throw;
            }
            
            return null;

        }

        protected void SqlConnectionReset()
        {
            //if the connection is null we create masterAttributeMetadata new one
            if (sqlcn == null)
            {
                sqlcn = new SqlConnection(ConnectionString);
            }
            //if the connection is open we close it
            if (sqlcn.State == ConnectionState.Open)
            {
                sqlcn.Close();
            }

            //if the connection is not closed and we also know it was neither open then we assume it is in as invalid state, so we reinitialize the connection
            if (sqlcn.State != ConnectionState.Closed)
            {
                sqlcn.Dispose();
                sqlcn = new SqlConnection(ConnectionString);
            }

            //finally after controlling all state we open the connection
            sqlcn.Open();
        
        }
    }
}
